﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using iTextSharp.text.pdf;
using iTextSharp.text;
using iTextSharp;
using System.IO;
namespace Quality.Records
{
    public partial class reports : Form
    {
        DataTable data=new DataTable();
        public reports()
        {
            InitializeComponent();
          
            DB.getcombobox(shift, "shift", "num", "shiftname");
            DB.getcombobox(typenum, "testtypes", "num", "typename");
            DB.getcombobox(worknum, "workcenters", "num", "workname");
        }

     
        public void q(){
         if (string.IsNullOrEmpty(workdate.Text) || string.IsNullOrEmpty(workdate1.Text))
            {
                MessageBox.Show("生产日期不能为空！");
                return;
            }
            if (typenum.SelectedIndex == -1)
            {
                MessageBox.Show("检验类型不能为空！");
                return;

            }
            string s = "";

            if (!string.IsNullOrEmpty(num.Text))
            {
                s = s + " and a.num='" + num.Text.Trim() + "' ";

            }
            if (!string.IsNullOrEmpty(shift.Text))
            {
                s = s + " and a.shift='" + shift.SelectedValue + "' ";

            }
            if (!string.IsNullOrEmpty(pronum.Text))
            {
                s = s + " and a.pronum like '%" + pronum.Text.Trim() + "%' ";

            }
            if (!string.IsNullOrEmpty(proname.Text))
            {
                s = s + " and a.proname like '%" + proname.Text.Trim() + "%' ";

            }

            if (!string.IsNullOrEmpty(batch.Text))
            {
                s = s + " and a.batch like '%" + batch.Text.Trim() + "%' ";

            }
            if (!string.IsNullOrEmpty(typenum.Text))
            {
                s = s + " and a.typenum='" + typenum.SelectedValue + "' ";

            }
            if (!string.IsNullOrEmpty(itemnum.Text))
            {
                s = s + " and a.itemnum='" + itemnum.SelectedValue + "' ";

            }
            if (!string.IsNullOrEmpty(indnum.Text))
            {
                s = s + " and a.indnum='" + indnum.SelectedValue + "' ";

            }
            if (!string.IsNullOrEmpty(eqnum.Text))
            {
                s = s + " and a.eqnum='" + eqnum.SelectedValue + "' ";

            }
            if (!string.IsNullOrEmpty(worknum.Text))
            {
                s = s + " and c.num='" + worknum.SelectedValue + "' ";

            }

            data = sqlHelper.ExecuteDataTable("select a.num,a.rownum,a.workdate,a.shift,a.itemnum,a.indnum,a.pronum ,a.proname,a.batch,c.workname,b.eqname,d.typename,e.itemname,f.indname,f.indvalue,a.testvalue,h.cusname from records a left join equipments b on a.eqnum=b.num left join workcenters c on b.worknum=c.num  left join testtypes d on a.typenum=d.num  left join testitems e on a.itemnum=e.num left join  testindicators f on a.indnum=f.num left join products g on a.pronum=g.num left join customers h on g.customer=h.num where workdate>='" + workdate.Value + "' and workdate<='" + workdate1.Value + "' " + s + " order by a.pronum");
               
        
        }
        private void typenum_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (typenum.SelectedIndex != -1)
            {
                DataTable dt = sqlHelper.ExecuteDataTable("select num,itemname from testitems a left join assigneditems b on a.num=b.itemnum where b.typenum='" + typenum.SelectedValue + "'");
                itemnum.DataSource = dt;
                itemnum.DisplayMember = "itemname";
                itemnum.ValueMember = "num";
                itemnum.SelectedIndex = -1;

            }
        }

        private void itemnum_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (itemnum.SelectedIndex != -1)
            {
                DataTable dt = sqlHelper.ExecuteDataTable("select num,indname from testindicators a left join assignedindicators b on a.num=b.indnum where b.itemnum='" + itemnum.SelectedValue + "'");
                indnum.DataSource = dt;
                indnum.DisplayMember = "indname";
                indnum.ValueMember = "num";
                indnum.SelectedIndex = -1;

            }
        }

        private void worknum_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (worknum.SelectedIndex != -1)
            {

                DataTable dt = sqlHelper.ExecuteDataTable("select num,eqname from equipments  where worknum='" + worknum.SelectedValue + "'");
                eqnum.DataSource = dt;
                eqnum.DisplayMember = "eqname";
                eqnum.ValueMember = "num";
                eqnum.SelectedIndex = -1;
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {

            q();
            // 生成datatable
         

            DataTable listdt = new DataTable();//可以给表创建一个名字，datatable  
            //2.给表加个列名：  
            listdt.Columns.Add("kh", typeof(System.String));
            listdt.Columns.Add("pronum", typeof(System.String));//类型是可以变换的，比如System.Int32,System.Double..  
            listdt.Columns.Add("proname", typeof(System.String));

            listdt.Columns.Add("batch", typeof(System.String));


            DataTable itemdt = sqlHelper.ExecuteDataTable("select * from assigneditems where typenum='" + typenum.SelectedValue + "' order by itemnum");

            foreach (DataRow itemrow in itemdt.Rows)
            {
                DataTable inddt = sqlHelper.ExecuteDataTable("select * from assignedindicators where itemnum='" + itemrow["itemnum"] + "'order by indnum");

                foreach (DataRow indrow in inddt.Rows)
                {
                    listdt.Columns.Add(indrow["itemnum"].ToString() + indrow["indnum"].ToString(), typeof(System.String));
                  
                }
            }
           

          
            for (int i=0;i<=data.Rows.Count-1;i++)
            {
                DataRow datarow=data.Rows[i];
                if (listdt.Rows.Count <= 0)
                {
                    DataRow row = listdt.NewRow();
                    row[0] = datarow["cusname"];
                    row[1] = datarow["pronum"];
                    row[2] = datarow["proname"];
                    row[3] = datarow["batch"];
                    listdt.Rows.Add(row);

                }
                else 
                {
                    if (datarow["pronum"].ToString().Trim() != data.Rows[i - 1]["pronum"].ToString().Trim())
                    {
                        DataRow row = listdt.NewRow();
                        row[0] = datarow["cusname"];
                        row[1] = datarow["pronum"];
                        row[2] = datarow["proname"];
                        row[3] = datarow["batch"];
                        listdt.Rows.Add(row);
                    }

                }

                int n = 3;

                DataTable itemdt1 = sqlHelper.ExecuteDataTable("select * from assigneditems where typenum='" + typenum.SelectedValue + "' order by itemnum");



                foreach (DataRow itemrow in itemdt1.Rows)
                {
                    DataTable inddt1 = sqlHelper.ExecuteDataTable("select * from assignedindicators where itemnum='" + itemrow["itemnum"] + "'order by indnum");

                    foreach (DataRow indrow in inddt1.Rows)
                    {
                        n = n + 1;
                        if (datarow["itemnum"].ToString().Trim() == itemrow["itemnum"].ToString().Trim() && datarow["indnum"].ToString().Trim() == indrow["indnum"].ToString().Trim()) 
                        {

                            Boolean b = true;
                            foreach (DataRow r in listdt.Rows)
                            {

                                if (r[1].ToString().Trim() == datarow["pronum"].ToString().Trim())
                                {
                                   if (string.IsNullOrEmpty(r[n].ToString()))
                                   {

                                          r[n] = datarow["testvalue"];
                                          b = false;
                                          break;
                                    }
                              
                                }

                            }
                            if(b)
                            {
                                DataRow row = listdt.NewRow();
                                row[0] = datarow["cusname"];
                                row[1] = datarow["pronum"];
                                row[2] = datarow["proname"];
                                row[3] = datarow["batch"];
                                listdt.Rows.Add(row);

                                listdt.Rows[listdt.Rows.Count - 1][n] = datarow["testvalue"];
                            }
                                

                            
                        
                        
                        }

                    }
                }

            }


            if (listdt.Rows.Count > 0)
            {
                //生成PDF
                string fileName = string.Empty;
                SaveFileDialog dlg = new SaveFileDialog();
                dlg.FileName = typenum.Text + "检验记录表-" + DateTime.Now.ToString("yyyyMMddHHmmss");
                dlg.DefaultExt = ".pdf";
                dlg.Filter = "Text documents (.pdf)|*.pdf";
                Nullable<bool> result = Convert.ToBoolean(dlg.ShowDialog());
                if (result == true)
                {

                    //      string path = AppDomain.CurrentDomain.BaseDirectory + "PDF";

                    //if (!Directory.Exists(path))//如果不存在就创建file文件夹　　             　　              
                    //    Directory.CreateDirectory(path);//创建该文件夹　


                    //定义一个Document，并设置页面大小为A4，竖向
                    iTextSharp.text.Document doc = new Document(PageSize.A4.Rotate(), 5, 5, 5, 5);

                    //写实例
                    PdfWriter.GetInstance(doc, new FileStream(dlg.FileName, FileMode.Create));
                    //打开document
                    doc.Open();

                    int size = Convert.ToInt32(psize.Value);


                    double p = listdt.Rows.Count / Convert.ToDouble(size);

                    p = Math.Ceiling(p);

                    for (int i = 0; i < p; i++)
                    {
                        head(doc);
                        body(listdt, doc, i * size, (i + 1) * size);

                        doc.NewPage();

                    }

                    doc.Close();
                }
            }
            else {
                MessageBox.Show("没有符合条件的记录！");
            }


        }

      public void  head( iTextSharp.text.Document doc ){

          BaseFont bfChinese = BaseFont.CreateFont("C:\\WINDOWS\\Fonts\\simsun.ttc,1", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
          iTextSharp.text.Font font10Chinese = new iTextSharp.text.Font(bfChinese, 10, iTextSharp.text.Font.NORMAL, new iTextSharp.text.Color(0, 0, 0));
          iTextSharp.text.Font font8Chinese = new iTextSharp.text.Font(bfChinese, 8, iTextSharp.text.Font.NORMAL, new iTextSharp.text.Color(0, 0, 0));
          iTextSharp.text.Font font14Chinese = new iTextSharp.text.Font(bfChinese, 14, iTextSharp.text.Font.NORMAL, new iTextSharp.text.Color(0, 0, 0));
          iTextSharp.text.Font font6Chinese = new iTextSharp.text.Font(bfChinese, 6, iTextSharp.text.Font.NORMAL, new iTextSharp.text.Color(0, 0, 0));


               PdfPTable bt = new PdfPTable(3);
               bt.SetWidths(new int[] { 10, 40, 50 });
               bt.HorizontalAlignment = Element.ALIGN_CENTER;
               bt.WidthPercentage = 100;
               //加入logo
               iTextSharp.text.Image im = iTextSharp.text.Image.GetInstance(AppDomain.CurrentDomain.BaseDirectory +"/image/logo.jpg");
               im.WidthPercentage = 0;
               im.ScalePercent(0.09f*100);//设置图片比例
              
               
               PdfPCell cell = new PdfPCell();
               
               cell.AddElement(im);
               cell.Border = 0;
               bt.AddCell(cell);

                PdfPCell cellbt = new PdfPCell();
                Paragraph phrasebt = new Paragraph("深圳华特容器股份有限公司天健分公司", font10Chinese);
                Paragraph phrasebt1 = new Paragraph( typenum.Text + "检验记录表", font14Chinese);            
                 phrasebt.Alignment = Element.ALIGN_CENTER;
                 phrasebt1.Alignment = Element.ALIGN_CENTER; 
                cellbt.AddElement(phrasebt);
                cellbt.AddElement(phrasebt1);
                cellbt.Border = 0;
          
                bt.AddCell(cellbt);

                PdfPTable table1 = new PdfPTable(4);
                table1.HorizontalAlignment = Element.ALIGN_RIGHT;
                table1.WidthPercentage = 100;
                table1.SetWidths(new int[] { 15, 40, 15, 40 });
                table1.AddCell(new Phrase("生产日期:", font10Chinese));
                table1.AddCell(new Phrase(workdate.Value.ToString("yyyy-MM-dd") + " 至 " + workdate1.Value.ToString("yyyy-MM-dd"), font8Chinese));
                table1.AddCell(new Phrase("班次:", font10Chinese));
                table1.AddCell(new Phrase(shift.Text, font8Chinese));
                table1.AddCell(new Phrase("工作中心:", font10Chinese));
                table1.AddCell(new Phrase(worknum.Text, font8Chinese));
                table1.AddCell(new Phrase("设备:", font10Chinese));
                table1.AddCell(new Phrase(eqnum.SelectedText.Trim(), font8Chinese));
                table1.AddCell(new Phrase("检验类型:", font10Chinese));
                table1.AddCell(new Phrase(typenum.Text, font8Chinese));
                table1.AddCell(new Phrase("", font10Chinese));
                table1.AddCell(new Phrase("", font10Chinese));
                table1.AddCell(new Phrase("编制", font10Chinese));
                table1.AddCell(new Phrase("", font10Chinese));
                table1.AddCell(new Phrase("审核", font10Chinese));
                table1.AddCell(new Phrase("", font10Chinese));
                PdfPCell cellbt1 = new PdfPCell();
               
                cellbt1.AddElement(table1);
                cellbt1.Border = 0;
                bt.AddCell(cellbt1);

                doc.Add(bt);
               

                doc.Add(new Phrase("\n", font6Chinese));
               //生成表头
                 DataTable headdt =sqlHelper.ExecuteDataTable("select * from assigneditems a left join testitems b on a.itemnum=b.num where a.typenum='"+typenum.SelectedValue+"'");
                int n=headdt.Rows.Count+4;
                PdfPTable headtable = new PdfPTable(n);
                headtable.HorizontalAlignment = Element.ALIGN_CENTER;
                headtable.WidthPercentage = 100;
                int[] wh = new int[4];
                for (int i = 0; i<4; i++)
                {
                    
                    wh[i] = 35;

                }

                PdfPCell cell1= new PdfPCell();
                Paragraph phrase1 = new Paragraph("客户", font8Chinese);
                phrase1.Alignment = Element.ALIGN_CENTER;
                cell1.PaddingTop = 15;
                cell1.AddElement(phrase1);
               
                
                headtable.AddCell(cell1);
                PdfPCell cell2 = new PdfPCell();
                Paragraph phrase2 = new Paragraph("产品编码", font8Chinese);
                phrase2.Alignment = Element.ALIGN_CENTER;
                cell2.AddElement(phrase2);
                cell2.PaddingTop = 15;
               
                headtable.AddCell(cell2);
                PdfPCell cell3 = new PdfPCell();
                Paragraph phrase3= new Paragraph("产品名称", font8Chinese);
                phrase3.Alignment = Element.ALIGN_CENTER;
                cell3.AddElement(phrase3);
                cell3.PaddingTop = 15;
             
                headtable.AddCell(cell3);
                PdfPCell cell4 = new PdfPCell();
                Paragraph phrase4 = new Paragraph("批次", font8Chinese);
                phrase4.Alignment = Element.ALIGN_CENTER;
                cell4.AddElement(phrase4);
                cell4.PaddingTop = 15;
               
                headtable.AddCell(cell4);

              

                foreach (DataRow headrow in headdt.Rows) {

                    PdfPTable headtable1 = new PdfPTable(1);
                    headtable1.HorizontalAlignment = Element.ALIGN_CENTER;
                    headtable1.WidthPercentage = 100;
                    PdfPCell cell5 = new PdfPCell();
                    Paragraph phrase5 = new Paragraph(headrow["itemname"].ToString(), font8Chinese);
                    phrase5.Alignment = Element.ALIGN_CENTER;
                    cell5.AddElement(phrase5);
                    cell5.PaddingBottom = 10;
                    cell5.Border = 0;
                    cell5.BorderWidthBottom = 0.5f;
                    headtable1.AddCell(cell5);

                    DataTable headdt1 = sqlHelper.ExecuteDataTable("select * from assignedindicators a left join testindicators b on a.indnum=b.num where a.itemnum='" + headrow["itemnum"].ToString() + "'");
                    int n1 = headdt1.Rows.Count ;
                    int[] w= new int[n1];
                    for (int i = 0; i != w.Length; i++)
                    {
                       w[i] =25;

                    }
                    wh = wh.Concat(new int[] { n1*25 }).ToArray();



                    PdfPTable headtable2 = new PdfPTable(n1);
                    headtable2.HorizontalAlignment = Element.ALIGN_CENTER;
                    headtable2.WidthPercentage = 100;
                    headtable2.SetWidths(w);
                     int j=0;
                    foreach (DataRow headrow1 in headdt1.Rows)
                    {
                        PdfPCell cell6 = new PdfPCell();
                        Paragraph phrase6 = new Paragraph(headrow1["indname"].ToString() + "\n" + headrow1["indvalue"].ToString(), font6Chinese);
                        phrase6.Alignment = Element.ALIGN_CENTER;
                        cell6.AddElement(phrase6);
                        cell6.Border = 0;
                        
                        if (j != headdt1.Rows.Count - 1)
                        {
                            cell6.BorderWidthRight= 0.5f;
                        }
                        j=j+1;
                        headtable2.AddCell(cell6);                       

                    }

                    PdfPCell cell7 = new PdfPCell();
                   
                    cell7.AddElement(headtable2);
                    cell7.Border = 0;
                    cell7.Padding = 0;
                    headtable1.AddCell(cell7);

                   headtable.AddCell(headtable1);
                }
                headtable.SetWidths(wh);

                doc.Add(headtable);

        }
      public void body(DataTable listdt, iTextSharp.text.Document doc,int start,int end)
      {
          //生成表体
          BaseFont bfChinese = BaseFont.CreateFont("C:\\WINDOWS\\Fonts\\simsun.ttc,1", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
          iTextSharp.text.Font font10Chinese = new iTextSharp.text.Font(bfChinese, 10, iTextSharp.text.Font.NORMAL, new iTextSharp.text.Color(0, 0, 0));
          iTextSharp.text.Font font8Chinese = new iTextSharp.text.Font(bfChinese, 8, iTextSharp.text.Font.NORMAL, new iTextSharp.text.Color(0, 0, 0));
          iTextSharp.text.Font font14Chinese = new iTextSharp.text.Font(bfChinese, 14, iTextSharp.text.Font.NORMAL, new iTextSharp.text.Color(0, 0, 0));
          iTextSharp.text.Font font6Chinese = new iTextSharp.text.Font(bfChinese, 6, iTextSharp.text.Font.NORMAL, new iTextSharp.text.Color(0, 0, 0));
          int nn = listdt.Columns.Count;
          PdfPTable bodytable = new PdfPTable(nn);
          bodytable.HorizontalAlignment = Element.ALIGN_CENTER;
          bodytable.WidthPercentage = 100;
          int[] ww = new int[nn];
          for (int i = 0; i != ww.Length; i++)
          {
              if (i < 4)
              {
                  ww[i] = 35;
              }
              else
              {

                  ww[i] = 25;

              }


          }
          bodytable.SetWidths(ww);

          for (int i = start; i < end;i++ )
          {
              if (i == listdt.Rows.Count) {

                  break;
              
              };

              foreach (DataColumn col in listdt.Columns)
              {


                  PdfPCell cell = new PdfPCell();
                  Paragraph phrase = new Paragraph(listdt.Rows[i][col].ToString(), font6Chinese);
                  phrase.Alignment = Element.ALIGN_CENTER;
                  cell.AddElement(phrase);

                  bodytable.AddCell(cell);

              }

          }

          doc.Add(bodytable);
         
      }

      private void button2_Click(object sender, EventArgs e)
      {
          q();

          if (data.Rows.Count > 0)
          {
              string fileName = string.Empty;
              SaveFileDialog dlg = new SaveFileDialog();
              dlg.FileName = typenum.Text + "检验记录明细表-" + DateTime.Now.ToString("yyyyMMddHHmmss");
              dlg.DefaultExt = ".xls";
              Nullable<bool> result = Convert.ToBoolean(dlg.ShowDialog());
              if (result == true)
              {
                  ExportExcel.ExportExcelWithAspose(data, dlg.FileName);

              }
          }
          else
          {

              MessageBox.Show("没有符合条件的记录！");

          }
      }

      private void button3_Click(object sender, EventArgs e)
      {
          q();
          // 生成datatable


          DataTable listdt = new DataTable();//可以给表创建一个名字，datatable  
          //2.给表加个列名：  
          listdt.Columns.Add("客户", typeof(System.String));
          listdt.Columns.Add("产品编码", typeof(System.String));//类型是可以变换的，比如System.Int32,System.Double..  
          listdt.Columns.Add("产品名称", typeof(System.String));

          listdt.Columns.Add("批次", typeof(System.String));


          DataTable itemdt = sqlHelper.ExecuteDataTable("select * from assigneditems a left join testitems  b on a.itemnum=b.num where a.typenum='" + typenum.SelectedValue + "' order by a.itemnum");

          foreach (DataRow itemrow in itemdt.Rows)
          {
              DataTable inddt = sqlHelper.ExecuteDataTable("select * from assignedindicators a left join testindicators  b on a.indnum=b.num where a.itemnum='" + itemrow["itemnum"] + "'order by a.indnum");

              foreach (DataRow indrow in inddt.Rows)
              {
                  listdt.Columns.Add(itemrow["itemname"].ToString() +"["+ indrow["indname"].ToString()+"]", typeof(System.String));

              }
          }



          for (int i = 0; i <= data.Rows.Count - 1; i++)
          {
              DataRow datarow = data.Rows[i];
              if (listdt.Rows.Count <= 0)
              {
                  DataRow row = listdt.NewRow();
                  row[0] = datarow["cusname"];
                  row[1] = datarow["pronum"];
                  row[2] = datarow["proname"];
                  row[3] = datarow["batch"];
                  listdt.Rows.Add(row);

              }
              else
              {
                  if (datarow["pronum"].ToString().Trim() != data.Rows[i - 1]["pronum"].ToString().Trim())
                  {
                      DataRow row = listdt.NewRow();
                      row[0] = datarow["cusname"];
                      row[1] = datarow["pronum"];
                      row[2] = datarow["proname"];
                      row[3] = datarow["batch"];
                      listdt.Rows.Add(row);
                  }

              }

              int n = 3;

              DataTable itemdt1 = sqlHelper.ExecuteDataTable("select * from assigneditems where typenum='" + typenum.SelectedValue + "' order by itemnum");



              foreach (DataRow itemrow in itemdt1.Rows)
              {
                  DataTable inddt1 = sqlHelper.ExecuteDataTable("select * from assignedindicators where itemnum='" + itemrow["itemnum"] + "'order by indnum");

                  foreach (DataRow indrow in inddt1.Rows)
                  {
                      n = n + 1;
                      if (datarow["itemnum"].ToString().Trim() == itemrow["itemnum"].ToString().Trim() && datarow["indnum"].ToString().Trim() == indrow["indnum"].ToString().Trim())
                      {

                          Boolean b = true;
                          foreach (DataRow r in listdt.Rows)
                          {

                              if (r[1].ToString().Trim() == datarow["pronum"].ToString().Trim())
                              {
                                  if (string.IsNullOrEmpty(r[n].ToString()))
                                  {

                                      r[n] = datarow["testvalue"];
                                      b = false;
                                      break;

                                  }


                              }

                          }
                          if (b)
                          {
                              DataRow row = listdt.NewRow();
                              row[0] = datarow["cusname"];
                              row[1] = datarow["pronum"];
                              row[2] = datarow["proname"];
                              row[3] = datarow["batch"];
                              listdt.Rows.Add(row);

                              listdt.Rows[listdt.Rows.Count - 1][n] = datarow["testvalue"];
                          }





                      }

                  }
              }

          }
          if (listdt.Rows.Count > 0)
          {
              string fileName = string.Empty;
              SaveFileDialog dlg = new SaveFileDialog();
              dlg.FileName = typenum.Text + "检验记录汇总表-" + DateTime.Now.ToString("yyyyMMddHHmmss");
              dlg.DefaultExt = ".xls";
              Nullable<bool> result = Convert.ToBoolean(dlg.ShowDialog());
              if (result == true)
              {
                  ExportExcel.ExportExcelWithAspose(listdt, dlg.FileName);

              }
          }
          else {

              MessageBox.Show("没有符合条件的记录！");
          }
      }
    }
}
